﻿Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataProvider
#Region "Biến"
    Public Shared SqlConnectionStr As String
    Public Shared connect As New SqlConnection
    Private _error As String
    Public maychu As String
    Public csdl As String

#End Region

    Public Sub DataProvider()


        connect = New SqlConnection(SqlConnectionStr)
    End Sub


    Public Function Ketnoi(ByVal Maychu As String, ByVal CSDL As String) As String
        Dim strCon As String
        Dim strError As String = ""
        strCon = "Server='" & Maychu & "';"
        strCon += "database='" & CSDL & "';"
        strCon += "integrated security=SSPI;"

        connect = New SqlConnection(strCon)

        Try

            connect.Open()

        Catch ex As Exception
            strError = ex.Message
        End Try
        Return strError
    End Function

    Public Function OpenConnect() As Boolean

        Try
            If connect Is Nothing Then
                connect = New SqlConnection(SqlConnectionStr)
            End If
            If connect.State = ConnectionState.Open Then
                connect.Close()
            End If

            connect.Open()
            Return True
        Catch ex As Exception
            _error = ex.Message
            Return False
        End Try
    End Function

    'Execute Non Query Command text
    Public Function ExecuteNonQuery(ByVal sql As String) As Boolean
        Try
            If OpenConnect() Then
                Dim cmd As SqlCommand = connect.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sql
                cmd.ExecuteNonQuery()
                _error = ""
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            _error = ex.Message
            Throw New Exception(_error)
            Return False
        Finally
            connect.Close()
        End Try
    End Function

    'Execute Non Query Command Store Procedure
    Public Function ExecuteNonQuery(ByVal procName As String, ByVal param As SqlParameter()) As Boolean
        Try
            If OpenConnect() Then
                Dim cmd As SqlCommand = connect.CreateCommand()
                cmd.CommandType = CommandType.StoredProcedure

                If Not param Is Nothing Then
                    cmd.Parameters.AddRange(param)
                End If

                cmd.CommandText = procName
                cmd.ExecuteNonQuery()
                _error = ""
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            _error = ex.Message
            Throw New Exception(_error)
            Return False
        Finally
            connect.Close()
        End Try
    End Function

    'Execute Query Command text
    Public Function ExecuteQuery(ByVal sql As String) As DataTable
        Dim dt As New DataTable

        Try
            If OpenConnect() Then
                Dim cmd As SqlCommand = connect.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sql
                Dim da As New SqlDataAdapter(cmd)
                da.Fill(dt)
                _error = ""
                Return dt
            Else
                Return dt
            End If
        Catch ex As Exception
            _error = ex.Message
            Return dt
        Finally
            connect.Close()
        End Try
    End Function

    'Execute Query Command Store Procedure
    Public Function ExecuteQuery(ByVal procName As String, ByVal param As SqlParameter()) As DataTable

        Dim dt As New DataTable
        Try
            If OpenConnect() Then
                Dim cmd As SqlCommand = connect.CreateCommand()
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = procName
                If Not param Is Nothing Then
                    cmd.Parameters.AddRange(param)
                End If

                Dim da As New SqlDataAdapter(cmd)
                da.Fill(dt)
                _error = ""
            End If
        Catch ex As Exception
            _error = ex.Message
            Throw New Exception(_error)
        Finally
            connect.Close()
        End Try
        Return dt
    End Function
End Class
